
# import all packages
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import os


import seaborn as sns
import time
import glob

# clear the garbage to free memory as we are working with huge datasets
import gc 

# Import custom modules
from src.utils import reduce_mem_usage, create_folder, change_width
import src.visuals as vs

# set plots to be embedded inline
%matplotlib inline

# suppress matplotlib user warnings
import warnings
warnings.filterwarnings("ignore", category=UserWarning, module="matplotlib")

# Make your Jupyter Notebook wider
from IPython.display import display, HTML
display(HTML('<style>.container { width:80% !important; }</style>'))

# environment settings
# display all columns and rows during visual inspection
pd.options.display.max_columns = 4000
pd.options.display.max_rows = 20000
pd.options.display.max_colwidth = 4000

from pylab import rcParams
from statsmodels.graphics import tsaplots
import statsmodels.api as sm


# from importlib import reload
# reload(vs)


# set default plot formatting
BASE_COLOR, BASE_HIGHLIGHT_INTENSE, BASE_HIGHLIGHT, BASE_COMPLEMENTRY, BASE_GREY, BASE_COLOR_ARR, BASE_COLOR_DEP, SYMBOLS = vs.set_plot_defaults()


# default file paths
FILE_NAME_RAW = '../data/flights_raw.pkl'
FILE_NAME_CLEAN = '../data/flights_clean.pkl'
FILE_NAME_ENHANCED = '../data/flights_enhanced.pkl'


# load the cleaned file 
flights = pd.read_pickle(FILE_NAME_CLEAN)
flights.sample(5)


# make sure datatypes were preserved from cleaning step
flights.info(verbose=True, show_counts=True)

<class 'pandas.core.frame.DataFrame'>
Int64Index: 37701228 entries, 0 to 37742759
Data columns (total 29 columns):
 #   Column             Non-Null Count     Dtype          
---  ------             --------------     -----          
 0   year               37701228 non-null  int16          
 1   month              37701228 non-null  int8           
 2   dayofMonth         37701228 non-null  int8           
 3   dayOfWeek          37701228 non-null  int8           
 4   depTime            36991149 non-null  timedelta64[ns]
 5   CRSDepTime         37701228 non-null  timedelta64[ns]
 6   arrTime            36913965 non-null  timedelta64[ns]
 7   CRSArrTime         37701228 non-null  timedelta64[ns]
 8   uniqueCarrier      37701228 non-null  object         
 9   flightNum          37701228 non-null  int16          
 10  tailNum            37632291 non-null  object         
 11  actualElapsedTime  36913965 non-null  float16        
 12  CRSElapsedTime     37700823 non-null  float16        
 13  airTime            36913965 non-null  float16        
 14  arrDelay           36913965 non-null  float16        
 15  depDelay           36991149 non-null  float16        
 16  origin             37701228 non-null  object         
 17  dest               37701228 non-null  object         
 18  distance           37701228 non-null  int16          
 19  taxiIn             37631406 non-null  float16        
 20  taxiOut            37636786 non-null  float16        
 21  cancelled          37701228 non-null  bool           
 22  cancellationCode   661712 non-null    object         
 23  diverted           37701228 non-null  bool           
 24  carrierDelay       37701228 non-null  float16        
 25  weatherDelay       37701228 non-null  float16        
 26  NASDelay           37701228 non-null  float16        
 27  securityDelay      37701228 non-null  float16        
 28  lateAircraftDelay  37701228 non-null  float16        
dtypes: bool(2), float16(12), int16(3), int8(3), object(5), timedelta64[ns](4)
memory usage: 4.0+ GB


flights.shape

(37701228, 29)


# get carrier descriptions
carriers = pd.read_csv('../data/lookup_tables/carriers.csv', index_col='Code')
carriers = carriers.rename(columns={'Description': 'carrier_description'})
carriers.index.name = 'uniqueCarrier'
carriers.fillna('unknown', axis=1, inplace=True)
carriers.sample(5)


# get airplane data
planes = pd.read_csv('../data/lookup_tables/plane-data.csv', index_col='tailnum')
planes.fillna('unknown', axis=1, inplace=True)
planes.sample(10)


planes = planes.rename(columns={'type': 'plane_type'})


planes.status.value_counts()

Valid                4479
unknown               549
Registered to Mfr       1
Name: status, dtype: int64


# drop columns not needed
planes.drop('status', axis=1, inplace=True)


# get airport descriptions
airports = pd.read_csv('../data/lookup_tables/airports.csv', index_col='iata')
airports.fillna('unknown', axis=1, inplace=True)
airports.sample(10)


airports.country.value_counts()

USA                               3372
Thailand                             1
Palau                                1
N Mariana Islands                    1
Federated States of Micronesia       1
Name: country, dtype: int64


# destination airport CBM are not present in airports dataset, and only 1 flight exist. Delete the record as it causes issues during analysis below
flights = flights.loc[flights.dest != 'CBM']


# create a datetime field for time series analysis
flights.rename(columns={'dayofMonth':'day'}, inplace=True)
flights['hour'] = flights['depTime'].dt.components['hours']
flights['dateh'] = pd.to_datetime(flights[['year', 'month', 'day', 'hour']], yearfirst=True, errors='raise')
flights['date'] = pd.to_datetime(flights[['year', 'month', 'day']], yearfirst=True, errors='raise')
flights[['year', 'month', 'day', 'depTime', 'date', 'dateh']].sample(5)


# build a flight status field to compare cancelled, diverted, ontime and delayed flights
flights['flight_status'] = np.where(flights['cancelled'] == True, 'cancelled',
                           np.where(flights['diverted'] == True, 'diverted',
                           np.where((flights['cancelled'] == False) & (flights['diverted'] == False) & (flights['arrDelay'] >= 15), 'delayed',    
                           np.where((flights['cancelled'] == False) & (flights['diverted'] == False) & (flights['arrDelay'] < 15), 'on_time', np.nan))))  

flights['flight_status'].value_counts()

on_time      28987263
delayed       7926701
cancelled      710079
diverted        77184
Name: flight_status, dtype: int64


# add new 'lane' feature consisting of both departure and arrival airport
flights['lane'] = flights['origin'] + '-' + flights['dest']


# drop columns that will not be used further for this analysis
cols_to_drop = ['CRSDepTime', 'CRSArrTime', 'CRSElapsedTime', 'flightNum']
flights.drop(cols_to_drop, axis=1, inplace=True)


flights.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 37701227 entries, 0 to 37742759
Data columns (total 30 columns):
 #   Column             Dtype          
---  ------             -----          
 0   year               int16          
 1   month              int8           
 2   day                int8           
 3   dayOfWeek          int8           
 4   depTime            timedelta64[ns]
 5   arrTime            timedelta64[ns]
 6   uniqueCarrier      object         
 7   tailNum            object         
 8   actualElapsedTime  float16        
 9   airTime            float16        
 10  arrDelay           float16        
 11  depDelay           float16        
 12  origin             object         
 13  dest               object         
 14  distance           int16          
 15  taxiIn             float16        
 16  taxiOut            float16        
 17  cancelled          bool           
 18  cancellationCode   object         
 19  diverted           bool           
 20  carrierDelay       float16        
 21  weatherDelay       float16        
 22  NASDelay           float16        
 23  securityDelay      float16        
 24  lateAircraftDelay  float16        
 25  hour               float64        
 26  dateh              datetime64[ns] 
 27  date               datetime64[ns] 
 28  flight_status      object         
 29  lane               object         
dtypes: bool(2), datetime64[ns](2), float16(11), float64(1), int16(2), int8(3), object(7), timedelta64[ns](2)
memory usage: 4.7+ GB


# save the enhanced flight file as pickle file
flights.to_pickle(FILE_NAME_ENHANCED)


# clear the garbage to free memory
gc.collect()

20


# reload the enhanced cleaned flight dataset
flights = pd.read_pickle(FILE_NAME_ENHANCED)
flights.sample(5)


# calculate number of flights per flight status and plot it
flight_status_summary = flights['flight_status'].value_counts(normalize=True)
flight_status = flights['flight_status'].value_counts()
sns.barplot(x=flight_status.index, y=flight_status, color=BASE_COLOR)
plt.ylabel('')
plt.xlabel('Flight status')
plt.title('Proportions of flight status')

locs, labels = plt.xticks()

# for each bar, print a % text at the top of each bar
for loc, label in zip(locs,labels):
    count = flight_status[label.get_text()]
    perc = flight_status_summary[label.get_text()]
    pct_string = '{:0.1f}%'.format(perc*100)
    plt.text(loc, count+0.01, pct_string, ha='center', color='black', size=8, weight='ultralight')
    
ticks = np.arange(0, flight_status[0], 5000000)
labels = ['{:1.0f} mil'.format(tick/1000000) for tick in ticks]
plt.yticks(ticks,labels)

plt.show()


# Take a closer look at distribution of all flights
flights[['depDelay', 'arrDelay']].describe([0.25, 0.5, 0.75, 0.85, 0.9, 0.95, 0.99, 0.999]).round(0)


# plot distribution of arrival and departure delays using boxplots
fig, ax = plt.subplots(ncols=2, figsize=(16,4))   

plt.subplot(1,2,1)
sns.boxplot(data=flights, x='depDelay', color = BASE_COLOR_DEP, flierprops={"marker": "x"}, medianprops={"color": BASE_HIGHLIGHT })

plt.subplot(1,2,2)
sns.boxplot(data=flights, x='arrDelay', color = BASE_COLOR_ARR, flierprops={"marker": "x"}, medianprops={"color": BASE_HIGHLIGHT })

plt.suptitle('Distributions of arrival and departure delays')
plt.show()


# drop outliers
# get rows to drop where delays are >= 310 minutes
rows_to_drop = flights.loc[(flights['depDelay'] >= 310) | (flights['arrDelay'] >= 310)]
flights.drop(index=rows_to_drop.index, inplace=True)


# extract delayed flights into a seperate dataset
flight_delays = flights.loc[flights['flight_status'] == 'delayed'].copy()


# get all columns of type float
float_columns = list(flight_delays.select_dtypes(include=['float16']).columns)

# convert all float columns to int16, which should now be possible as cancelled and diverted data had a lot of missing values in
flight_delays[float_columns] = flight_delays[float_columns].astype('int16')


plt.figure(figsize=[12,4])

xbins = np.arange(0, flight_delays['arrDelay'].max()+15, 15)

# plot 1 - distribution of departure delays
ax1 = plt.subplot(1, 2, 1)
ax1.hist(data=flight_delays, x='depDelay', bins=xbins, color=BASE_COLOR_DEP)

plt.xlabel('Departure delays (in minutes)')
plt.ylabel('Number of flights')
plt.xlim(0, 300)
plt.title('Distribution of departure delays')

# plot 2 - distribution of arrival delays
ax2 = plt.subplot(1, 2, 2, sharey=ax1, sharex=ax1)
ax2.hist(data=flight_delays, x='arrDelay', bins=xbins, color=BASE_COLOR_ARR)
plt.xlabel('Arrival delays (in minutes)')
plt.title('Distribution of arrival delays')
plt.ticklabel_format(style='plain', axis='y')

plt.tight_layout()
plt.show()


# plot log distribution of delays

plt.figure(figsize=[12,4])

log_binsize = 0.1
bins= 10 ** np.arange(0, np.log10(flight_delays['arrDelay'].max())+log_binsize, log_binsize)
ticks = [1, 3, 10, 30, 100, 300, 1000]
labels = ['{}'.format(tick) for tick in ticks]

ax2 = plt.subplot(1, 2, 2)
plt.hist(data=flights.loc[flights['flight_status'] == 'on_time'], x='arrDelay', bins=bins, color=BASE_COLOR_ARR, alpha=0.2)
plt.hist(data=flights.loc[flights['flight_status'] == 'delayed'], x='arrDelay', bins=bins, color=BASE_COLOR_ARR)
plt.xscale('log')
plt.xticks(ticks, labels)
plt.xlabel('Arrival delays (in minutes)')
plt.ylabel('Number of flights')
plt.title('Log Distribution of arrival delays')
plt.ticklabel_format(style='plain', axis='y')
plt.xlim(1, 300)

ax1 = plt.subplot(1, 2, 1, sharex=ax2, sharey=ax2)
plt.hist(data=flights.loc[flights['flight_status'] == 'delayed'], x='depDelay', bins=bins, color=BASE_COLOR_DEP)
plt.xticks(ticks, labels)
plt.xlabel('Departure delays (in minutes)')
plt.ylabel('Number of flights')
plt.title('Log Distribution of departure delays')
plt.ticklabel_format(style='plain', axis='y')

plt.tight_layout()


# drop records where all delay reasons are 0, means reasons are not available
delay_reasons = flight_delays.loc[flight_delays[['carrierDelay','weatherDelay','NASDelay','securityDelay','lateAircraftDelay']].sum(axis=1) != 0]


# calculate the mean minutes and proportion of each reason
delay_reason_melt = pd.melt(delay_reasons, value_vars = ['carrierDelay','weatherDelay','NASDelay','securityDelay','lateAircraftDelay'], value_name='minutes')
delay_reason_melt = delay_reason_melt.groupby('variable')['minutes'].agg(['sum', 'mean'])
delay_reason_melt['perc'] = delay_reason_melt['sum']/delay_reason_melt['sum'].sum()*100
delay_reason_melt.sort_values(by='perc', ascending=False, inplace=True)
delay_reason_melt.head()


sns.barplot(data=delay_reason_melt, x=delay_reason_melt.index, y='mean', color=BASE_COLOR)

plt.title('Distribution for reasons of delays')
plt.xlabel('Reasons for delay')
plt.ylabel('Average delays (in minutes)')

# for each bar, print % of total delay at the top of each bar
locs, labels = plt.xticks()
for loc, label in zip(locs,labels):
    bar = delay_reason_melt.loc[label.get_text()]
    pct_string = '{:0.0f}min / {:0.0f}%'.format(bar['mean'], bar['perc'])
    plt.text(loc, bar['mean']+0.1, pct_string, ha='center', color='black', size=8, weight='ultralight')

plt.show()


for col in ['lateAircraftDelay', 'NASDelay', 'carrierDelay']:
     
    plt.figure(figsize=[12,4])  
    
    # plot 2 - log distribution for reason of delay
    ax2 = plt.subplot(1, 2, 2)    
       
    log_binsize = 0.1
    bins= 10 ** np.arange(0, np.log10(delay_reasons[col].max())+log_binsize, log_binsize)
    ticks = [1, 3, 10, 30, 100, 300, 1000]
    labels = ['{}'.format(tick) for tick in ticks]

    plt.hist(data=delay_reasons, x=col, bins=bins, color=BASE_COLOR_ARR)
    plt.xscale('log')
    plt.xticks(ticks,labels)
    plt.xlabel('Arrival delays of {} (in minutes)'.format(col))
    plt.title('Log distribution of {}'.format(col))    
    
    # plot 1 - distribution of reason for delay
    xbins = np.arange(0, delay_reasons[col].max()+5, 5)

    ax1 = plt.subplot(1, 2, 1, sharey=ax2)
    plt.hist(data=delay_reasons.loc[delay_reasons[col] > 0], x=col, bins=xbins, color=BASE_COLOR_DEP)
      
    plt.xlabel('Arrival delays of {} (in minutes)'.format(col))
    plt.ylabel('Number of flights')
    plt.xlim(0, 300)
    plt.title('Distribution of {}'.format(col))
    
    plt.tight_layout()


# build a timeseries dataset with datatime field as index
flight_timeseries_day = flights.groupby('dateh')['flight_status'].value_counts().unstack()
flight_timeseries_day['total_flights'] = flight_timeseries_day.sum(axis=1)
flight_timeseries_day['diff_delay'] = flight_timeseries_day['delayed'].diff()
flight_timeseries_day['pct_delay'] = flight_timeseries_day['delayed'].pct_change()
flight_timeseries_day.head()


flight_timeseries_day.tail()


# drop 2008 from all data as it's not a full year
flight_timeseries_day = flight_timeseries_day.loc[flight_timeseries_day.index.year < 2008]
flights = flights.loc[flights['year'] < 2008]
flight_delays = flight_delays.loc[flight_delays['year'] < 2008]


# create a yearly time series
freq = 'Y'
flight_timeseries_year = flight_timeseries_day.resample(freq).sum()
flight_timeseries_year['pct_delay'] = flight_timeseries_year['delayed'].pct_change()
flight_timeseries_year['pct_all'] = flight_timeseries_year['total_flights'].pct_change()


# plot distribution of flights and delays year on year
plt.figure(figsize=[8,4])

sns.barplot(data=flight_timeseries_year, x=flight_timeseries_year.index.year, y='total_flights', color=BASE_GREY, label='All flights')
sns.barplot(data=flight_timeseries_year, x=flight_timeseries_year.index.year, y='delayed', color=BASE_COLOR, label='Delayed flights', width=0.6, edgecolor=BASE_COLOR)

# for each blue bar (delays), print % delays over total flights year on year 
locs, labels = plt.xticks()
for loc, label in zip(locs,labels):
    date = pd.to_datetime(label.get_text()).strftime('%Y')
    date = date + '-12-31'
    counts = flight_timeseries_year.loc[date]
    
    # add positive or negative sign
    symbol = ''
    if pd.isna(counts['pct_delay']):
        continue
    elif counts['pct_delay'] > 0:
        symbol = SYMBOLS[1]
    elif counts['pct_delay'] < 0:
        symbol = SYMBOLS[0]
    
    pct_string_delay = '{}{:0.2f}%'.format(symbol, round(counts['pct_delay']*100,2))
    pct_string_all = '{}{:0.2f}%'.format(symbol, round(counts['pct_all']*100,2))
    plt.text(loc, counts.delayed+100000, pct_string_delay, ha='center', color='black', fontsize=8)
    plt.text(loc, counts.total_flights+100000, pct_string_all, ha='center', color='black', fontsize=8)
    
binsize=1000000
yticks = np.arange(0, flight_timeseries_year['total_flights'].max()+binsize, binsize)
ylabels = ['{:1.0f}'.format(tick/1000000)+' mil' for tick in yticks]
plt.yticks(yticks, ylabels)    

plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.title('Growth of total flights vs Delays per year')
plt.xlabel('Year')
plt.ylabel('Number of flights')

plt.show()


# create a monthly time series
freq = 'M'
flight_timeseries_month = flight_timeseries_day.resample(freq).sum()
flight_timeseries_month['pct_delay'] = flight_timeseries_month['delayed'].pct_change()
flight_timeseries_month['pct_all'] = flight_timeseries_month['total_flights'].pct_change()
flight_timeseries_month['diff_delay'] = flight_timeseries_month['delayed'].diff()
flight_timeseries_month['pct_diff_delay'] = flight_timeseries_month['diff_delay'].pct_change()


# plot proportion total flights vs delayed flights per month
vs.plot_period_stacked(df=flight_timeseries_month, col=flight_timeseries_month.index.month, annotate=True, title='Month', rotate=False)


vs.plot_period_side_by_side(df=flight_timeseries_month, col=flight_timeseries_month.index.month, annotate=True, title='Month', rotate=False, sharey=False)


vs.plot_period_stacked(df=flight_timeseries_day, col=flight_timeseries_day.index.day, annotate=True, title='Day of Month', rotate=True)


vs.plot_period_side_by_side(df=flight_timeseries_day, col=flight_timeseries_day.index.day, annotate=False, title='Day of Month', rotate=True, sharey=False)


vs.plot_period_stacked(df=flight_timeseries_day, col=flight_timeseries_day.index.dayofweek, annotate=True, title='Day of Week', rotate=False)


vs.plot_period_side_by_side(df=flight_timeseries_day, col=flight_timeseries_day.index.dayofweek, annotate=True, title='Day of Week', rotate=False, sharey=False)


vs.plot_period_stacked(df=flight_timeseries_day, col=flight_timeseries_day.index.hour, annotate=False, title='Departure hour', rotate=False)


vs.plot_period_side_by_side(df=flight_timeseries_day, col=flight_timeseries_day.index.hour, annotate=True, title='Departure hour', rotate=True, sharey=False)


plt.errorbar(x=flight_timeseries_month.index, y=flight_timeseries_month['delayed'])
plt.title('Flights by month')
plt.ylabel('Number of flights')
plt.show()


# plotting partial autocorrelations
fig = tsaplots.plot_pacf(flight_timeseries_month['delayed'], lags=12, alpha=0.1, method = "ols")
plt.show()


# print seasonal patterns using stats models
plt.rc("figure", figsize=(12,8))
flight_timeseries_month.dropna(inplace=True)
decomposition = sm.tsa.seasonal_decompose(flight_timeseries_month['delayed'], extrapolate_trend='freq')

# Plot the trend
plt.rc("figure", figsize=(8,4))
trend = decomposition.trend
ax = trend.plot(color=BASE_COLOR)
plt.xlabel('Period')
plt.ylabel('Number of delays')
plt.show()


# Display the seasional pattern
season = decomposition.seasonal
ax = season.plot(color=BASE_COLOR)
plt.xlabel('Period')
plt.show()


# plot flight delay distribution for departure airports
vs.delays_by_cat(df=flight_delays, col='origin', title='Departure airports')


# plot distribution of delayed vs on-time flights for origin/departure airports
origin = flights.groupby('origin')['flight_status'].value_counts().unstack().sort_values(by='delayed', ascending=False)
origin['airport'] = origin.index + ':' + airports.loc[origin.index]['airport']
origin.reset_index(inplace=True)
origin.set_index('airport', inplace=True)
origin['prop_delayed'] = origin['delayed'] / (origin['delayed'] + origin['on_time'])
origin_prop = origin.sort_values(by='prop_delayed', ascending=False)
vs.plot_categories(df=origin_prop, title='Origin airport')


# plot flight delay distribution for arrival airports
vs.delays_by_cat(df=flight_delays, col='dest', title='Arrival airports')


# plot distribution of delayed vs on-time flights for destination airports
dest = flights.groupby('dest')['flight_status'].value_counts().unstack().sort_values(by='delayed', ascending=False)
dest['airport'] = dest.index + ':' + airports.loc[dest.index]['airport']
dest.reset_index(inplace=True)
dest.set_index('airport', inplace=True)
dest['prop_delayed'] = dest['delayed'] / (dest['delayed'] + dest['on_time'])
dest.sort_values(by='prop_delayed', ascending=False, inplace=True)
vs.plot_categories(df=dest, title='Destination/arrival airport')


# plot flight delay distribution by lane
vs.delays_by_cat(df=flight_delays, col='lane', title='Lanes (origin and destination)')


# plot distribution of delayed vs on-time flights by lane (departure + arrival airport)
lanes = flights.groupby('lane')['flight_status'].value_counts().unstack().sort_values(by='delayed', ascending=False)
lanes['prop_delayed'] = lanes['delayed'] / (lanes['delayed'] + lanes['on_time'])
lanes_prop = lanes.sort_values(by='prop_delayed', ascending=False)
vs.plot_categories(df=lanes_prop, title='Lanes')


# plot flight delay distribution by carrier
vs.delays_by_cat(df=flight_delays, col='uniqueCarrier', title='Carriers')


# plot distribution of delayed vs on-time flights by carrier
cat_carrier = flights.groupby('uniqueCarrier')['flight_status'].value_counts().unstack().sort_values(by='delayed', ascending=False)
cat_carrier['total_flights'] = cat_carrier.sum(axis=1).astype('int32')
cat_carrier = cat_carrier.merge(carriers, how='inner', on='uniqueCarrier')
cat_carrier = cat_carrier.reset_index()
cat_carrier['carrier'] = cat_carrier.uniqueCarrier + ':' + cat_carrier['carrier_description']
cat_carrier.set_index('carrier', inplace=True)
cat_carrier['prop_delayed'] = cat_carrier['delayed'] / (cat_carrier['delayed'] + cat_carrier['on_time'])
cat_carrier_prop = cat_carrier.sort_values(by='prop_delayed', ascending=False)
vs.plot_categories(df=cat_carrier_prop, title='Unique Carriers', topn=20, figsize=(16,6))


# aggration rule for all category plots
f = {'delayed':'sum', 'on_time':'sum'}


# get plane characteristics
plane_data = flights.groupby('tailNum')['flight_status'].value_counts().unstack().sort_values(by='delayed', ascending=False)

plane_data = plane_data.merge(planes, how='inner', right_on='tailnum', left_on='tailNum')

plane_data['year'] = plane_data['year'].replace('None', None)
plane_data['year_issue'] = plane_data['issue_date'].replace('None', None)

plane_data['date1'] = pd.to_datetime(plane_data['year_issue'], errors='coerce', format='%Y/%m/%d')
plane_data['date2'] = pd.to_datetime(plane_data['year_issue'], errors='coerce', format='%m/%d/%Y')
plane_data['year_issue'] = plane_data['date1'].fillna(plane_data['date2'])
plane_data.dropna(axis=0, inplace=True, subset=['year_issue', 'year'])

plane_data['year_issue'] = plane_data['year_issue'].dt.year
plane_data['year_issue'] = plane_data['year_issue'].astype(int)
plane_data['year'] = plane_data['year'].astype(int)


col = ['manufacturer', 'engine_type', 'model']
col.sort()

for c in col:
    df = plane_data.groupby(c).agg(f).sort_values(by='delayed', ascending=False)
    df.index = df.index.astype(str)
    vs.plot_categories(df=df, title=c, topn=10, orient='h',figsize=(8,6))


col = ['year', 'year_issue']
col.sort()

for c in col:
    df = plane_data.groupby(c).agg(f)
    df.index = df.index.astype(str)
    vs.plot_categories(df=df, title=c, topn=100, orient='h', rotate=True, figsize=(10,6))


# save the enhanced flight file where outliers was removed, as pickle file to reuse during explanatory analysis
flights.to_pickle(FILE_NAME_ENHANCED)


# clear the garbage to free memory
gc.collect()

19657


# remove cancelled and diverted flights from the dataset
flights = flights.loc[flights['flight_status'].isin(['on_time', 'delayed'])]


# selected numerical variables of interest
numeric_vars = ['arrDelay', 'depDelay', 'airTime', 'distance', 'taxiIn', 'taxiOut', 'carrierDelay', 'NASDelay', 'lateAircraftDelay']


# correlation plot for numeric values of interest using BOTH on_time and delayed flights
sns.clustermap(flights[numeric_vars].corr(), cmap="Spectral", center=0, annot=True, fmt = '.2f', figsize=(6,6), alpha=0.8)
plt.title('Correlation heatmap for delayed & on_time flights')
plt.show()

# reset default plot formatting back
BASE_COLOR, BASE_HIGHLIGHT_INTENSE, BASE_HIGHLIGHT, BASE_COMPLEMENTRY, BASE_GREY, BASE_COLOR_ARR, BASE_COLOR_DEP, SYMBOLS = vs.set_plot_defaults()


# take a sample of all (on_time and delayed) flights to speed up performance
sample = np.random.choice(flights.shape[0], 200000, replace=False)
flight_sample = flights.iloc[sample].copy()

flight_sample = flight_sample[numeric_vars].copy()
flight_sample.fillna(0, inplace=True)

# handle logscale
for col in numeric_vars:
    flight_sample[col] = np.where(flight_sample[col] == 0, 0.9, flight_sample[col])
   
flight_sample.shape

(200000, 9)


# get another quick overview of the relationships between numeric variables using scatter plots and distributions
sample = np.random.choice(flight_sample.shape[0], 10000, replace=False)
flight_sample_small = flight_sample.iloc[sample].copy()

sns.set(style='ticks')
g = sns.PairGrid(data = flight_sample_small, vars=numeric_vars, corner=True, dropna=True)
g.map_diag(sns.kdeplot, color=BASE_COLOR);
g.map_offdiag(sns.regplot, fit_reg=True, y_jitter=0.1, x_jitter=0.1, truncate=True, logx=True, scatter_kws={'s':20, 'edgecolor':'white', 'lw':0.2}, line_kws={'color': 'coral', 'lw':2}, color=BASE_COLOR)
plt.show()

# reset default plot formatting back
BASE_COLOR, BASE_HIGHLIGHT_INTENSE, BASE_HIGHLIGHT, BASE_COMPLEMENTRY, BASE_GREY, BASE_COLOR_ARR, BASE_COLOR_DEP, SYMBOLS = vs.set_plot_defaults()


fig, ax = plt.subplots(ncols=2, figsize=(16,4)) 

ticks = np.arange(0, int(flight_sample['arrDelay'].max()+10), 10)

plt.subplot(1,2,1)
sns.regplot(x=flight_sample['depDelay'], y=flight_sample['arrDelay'], y_jitter=0.2, x_jitter=0.2, fit_reg=True, truncate=True, 
            scatter_kws={'alpha':0.4, 's':10, 'edgecolor':'black', 'lw':0.2}, line_kws={'color': 'orange', 'lw':1})
plt.xlabel('Departure delays (in minutes)')
plt.ylabel('Arrival delays (in minutes)')
plt.title('Correlation between departure and arrival delay in minutes')
plt.yticks(ticks, ticks)
plt.xticks(ticks, ticks)
plt.xlim(0,180)
plt.ylim(0,180)

plt.subplot(1,2,2)
sns.regplot(x=flight_sample['depDelay'], y=flight_sample['arrDelay'], fit_reg=True, logx=True, y_jitter=0.2, x_jitter=0.2, truncate=True, 
            scatter_kws={'alpha':0.4, 's':10, 'edgecolor':'black', 'lw':0.2}, line_kws={'color': 'orange', 'lw':1})
plt.xlabel('Departure delays (in minutes)')
plt.ylabel('Arrival delays (in minutes)')
plt.title('Correlation between departure and arrival delay in minutes - LOG SCALE')
plt.yticks(ticks, ticks)
plt.xticks(ticks, ticks)
plt.xlim(0,180)
plt.ylim(0,180)

plt.show()


fig, ax = plt.subplots(ncols=2, figsize=(16,4)) 

xticks = np.arange(0, 5500, 500)
yticks = np.arange(0, 1100, 100)

ax = plt.subplot(1,2,1)
sns.regplot(x=flight_sample['distance'], y=flight_sample['airTime'], y_jitter=0.2, x_jitter=0.2, fit_reg=True, truncate=True, scatter_kws={'alpha':0.4, 's':10, 'edgecolor':'black', 'lw':0.2}, line_kws={'color': 'orange', 'lw':1})
plt.xticks(xticks, xticks)
plt.yticks(yticks, yticks)
plt.ylim(0,1000)
plt.xlabel('distance')
plt.ylabel('Air time (in minutes)')

plt.subplot(1,2,2, sharex=ax, sharey=ax)
sns.regplot(x=flight_sample['distance'], y=flight_sample['airTime'], fit_reg=True, logx=True, y_jitter=0.2, x_jitter=0.2, truncate=True, scatter_kws={'alpha':0.4, 's':10, 'edgecolor':'black', 'lw':0.2}, line_kws={'color': 'orange', 'lw':1})
plt.xlabel('distance')
plt.ylabel('Air time (in minutes)')
plt.title('Correlation between departure and arrival delay in minutes - LOG SCALE')

plt.show()

/var/folders/4p/r9m3gynn3wj6h4yh4_lwz5fw0000gn/T/ipykernel_1281/882253735.py:14: MatplotlibDeprecationWarning: Auto-removal of overlapping axes is deprecated since 3.6 and will be removed two minor releases later; explicitly call ax.remove() as needed.
  plt.subplot(1,2,2, sharex=ax, sharey=ax)


# observe trend of arrival vs departure delay for max 12 hours flight
xticks = np.arange(0, 12*60+15, 15)
plt.figure(figsize=(16,6))
sns.lineplot(data=flights, x='airTime', y=flight_delays['depDelay']/flight_delays['arrDelay'])
plt.xticks(xticks, xticks)
plt.xlim(0,480)
plt.xlabel('Air time (in minutes)')
plt.ylabel('Factor depDelay/arrDelay')
plt.title('Relationship between arrival delay and departure delay')
plt.show()


# get top 30 carriers causing delays
top_carrier = list(cat_carrier['uniqueCarrier'][:30])


# get top departure airports causing delays
top_origin = list(origin['origin'][:30])


# get top lanes causing delays
top_lane = list(lanes.index[:30])


# filter flights dataset on the top 30 airports and carriers causing the most delays
df_heatmap_top30 = flights[['uniqueCarrier', 'origin', 'lane', 'depDelay', 'carrierDelay', 'lateAircraftDelay', 'NASDelay']].copy()
df_heatmap_top30 = df_heatmap_top30.loc[(df_heatmap_top30['uniqueCarrier'].isin(top_carrier))]
df_heatmap_top30 = df_heatmap_top30.loc[(df_heatmap_top30['origin'].isin(top_origin))]


# Focus only on frequent flying carrier/origin combinations where number of flights exceed at least 200 for the 4 year period
df_heatmap_exclude = df_heatmap_top30.groupby(['uniqueCarrier', 'origin']).size().reset_index().rename(columns={0:'count'})
df_heatmap_exclude = df_heatmap_exclude[df_heatmap_exclude['count'] < 200]

df_heatmap_top30 = pd.merge(df_heatmap_top30, df_heatmap_exclude, on=['uniqueCarrier', 'origin'], how='outer', indicator=True)
df_heatmap_top30 = df_heatmap_top30.loc[df_heatmap_top30['_merge'] == 'left_only'].drop(['_merge', 'count'], axis=1)


# creat heatmap
df_heatmap_depDelay = df_heatmap_top30.pivot_table(values='depDelay',index='uniqueCarrier',columns='origin', aggfunc=np.mean, sort=False)

# first reorder origin airports from low to high mean delays
df_heatmap_depDelay = df_heatmap_depDelay.loc[:, df_heatmap_depDelay.max().sort_values().index]

# then reorder carriers from low to high mean delays
df_heatmap_depDelay = df_heatmap_depDelay.loc[df_heatmap_depDelay.max(axis=1).sort_values().index, :]


# plot carrier/departure airport heatmap
plt.figure(figsize = [15,15])

g = sns.heatmap(df_heatmap_depDelay, center=8, cmap='Spectral', linewidths=0.1, linecolor='lightgrey', square=True, mask=df_heatmap_depDelay <= 0, 
                annot=True, fmt=".0f", vmax=35, vmin=0, cbar_kws={"orientation": "vertical", "pad": 0.03, "shrink": 0.5})

# add descriptions to xticks
xticks = g.get_xticks()
xlabels = []
locs, labels = plt.xticks()
for loc, label in zip(locs,labels):  
    city = '{}:{}'.format(label.get_text(), airports.loc[label.get_text()]['airport'][:30])
    xlabels.append(city)
plt.xticks(xticks, xlabels, rotation=90)

# add descriptions to yticks
yticks = g.get_yticks()
ylabels = []
locs, labels = plt.yticks()
for loc, label in zip(locs,labels):  
    carrier = '{}:{}'.format(label.get_text(), carriers.loc[label.get_text()]['carrier_description'][:30])
    ylabels.append(carrier)
plt.yticks(yticks, ylabels, rotation=360)

# put xlabels on top
plt.title('Heatmap showing carrier and departure airport average delays in minutes for all flights')
g.set(xlabel=" ", ylabel=" ")
g.xaxis.tick_top()



# add footnote figure reference, as this plot will become the main reference for further analysis
footnote = "* bottom right quadrant are referred to as the 'problem quadrant' in further analysis below"
plt.text(s = footnote,
         x = 0.5,
         y = 0.4,
         fontname = 'Arial',
         fontstyle = 'italic',
         fontsize = 10,
         ha = 'left',
         color ='r',
         transform = fig.transFigure)



plt.xlabel('Origin Airport')
plt.ylabel('Carrier')
plt.tight_layout()
plt.show()


# get the carriers and airports combinations in the same sequence as in heatmap, from low to high average total delays
problem_carrier = [label.split(':')[0] for label in ylabels] 
problem_airport = [label.split(':')[0] for label in xlabels] 
problem_carrier_description = ylabels
problem_airport_description = xlabels


# select the 'best' and 'worse' carrier/airport combinations in the same sequence as in heatmap, so we can compare them
# for 'best', select the 4 largest airlines that does well accross all airports

topn_best_worse = 8
problem_carrier_best_worse = problem_carrier[5:10] + problem_carrier[topn_best_worse*-1:] 
problem_airport_best_worse = problem_airport[5:10] + problem_airport[topn_best_worse*-1:]  
problem_carrier_description_best_worse = ylabels[5:10] + ylabels[topn_best_worse*-1:]
problem_airport_description_best_worse = xlabels[5:10] + xlabels[topn_best_worse*-1:]

# create dataset with best and worse carrier/airport combinations using heatmap above as base
df_heatmap_topn_best_worse = df_heatmap_top30[['uniqueCarrier', 'origin', 'carrierDelay', 'lateAircraftDelay', 'NASDelay']].copy()
df_heatmap_topn_best_worse = df_heatmap_topn_best_worse.loc[(df_heatmap_topn_best_worse['uniqueCarrier'].isin(problem_carrier_best_worse))]
df_heatmap_topn_best_worse = df_heatmap_topn_best_worse.loc[(df_heatmap_topn_best_worse['origin'].isin(problem_airport_best_worse))]


# get the problem quadrant only, select the 'worst' carrier/airport combinations in the same sequence as in heatmap, so we can compare them
topn_worse = 10
problem_carrier_worse = problem_carrier[topn_worse*-1:]
problem_airport_worse = problem_airport[topn_worse*-1:]
problem_carrier_description_worse = ylabels[topn_worse*-1:]
problem_airport_description_worse = xlabels[topn_worse*-1:]

# create dataset focussing only on the worse carrier/airport combinations in the bottom right quadant of the heatmap above
df_heatmap_topn_worse = df_heatmap_top30[['uniqueCarrier', 'origin', 'carrierDelay', 'lateAircraftDelay', 'NASDelay']].copy()
df_heatmap_topn_worse = df_heatmap_topn_worse.loc[(df_heatmap_topn_worse['uniqueCarrier'].isin(problem_carrier_worse))]
df_heatmap_topn_worse = df_heatmap_topn_worse.loc[(df_heatmap_topn_worse['origin'].isin(problem_airport_worse))]


# aggregate new datasets from different angles to analyze reason for delays
f = {'carrierDelay':np.mean, 'lateAircraftDelay':np.mean, 'NASDelay':np.mean}
df_heatmap_topn_best_worse = df_heatmap_topn_best_worse.groupby(['uniqueCarrier', 'origin'], as_index=False).agg(f)
df_heatmap_topn_worse = df_heatmap_topn_worse.groupby(['uniqueCarrier', 'origin'], as_index=False).agg(f)
df_heatmap_top30_agg = df_heatmap_top30.groupby(['uniqueCarrier', 'origin'], as_index=False).agg(f)


df_heatmap_reason = df_heatmap_top30_agg.melt(id_vars=['uniqueCarrier', 'origin'], var_name='delay_reason', value_name='delay')
df_heatmap_reason = df_heatmap_reason.groupby(['uniqueCarrier', 'origin', 'delay_reason'], as_index=False)['delay'].agg(np.mean)


g = sns.FacetGrid(data = df_heatmap_reason, col='delay_reason', height=6, aspect=1, col_order=['lateAircraftDelay', 'NASDelay', 'carrierDelay'], despine=True)
g.map_dataframe(sns.barplot, y='origin', x='delay', errorbar=None, order=problem_airport, palette='viridis_r', alpha=0.7)
g.refline(x=df_heatmap_reason["delay"].mean(), linewidth=1, color='r')

plt.suptitle('Reason for delays at AIRPORTS')
g.set_yticklabels(labels=problem_airport_description)
g.set_axis_labels("Average delays (in minutes)", "Airport (average delays low -> high")

plt.tight_layout()
plt.show()


g = sns.FacetGrid(data = df_heatmap_reason, col='delay_reason', height=5, aspect=1, margin_titles=False, col_order=['lateAircraftDelay', 'NASDelay', 'carrierDelay'])
g.map_dataframe(sns.barplot, y='uniqueCarrier', x='delay', errorbar=None, order=problem_carrier, palette='viridis_r', alpha=0.7)
g.refline(x=df_heatmap_reason["delay"].mean(), linewidth=1, color='r')

plt.suptitle('Reason for delays for CARRIERS')
g.set_yticklabels(labels=problem_carrier_description)
g.set_axis_labels("Average delays (in minutes)", "Carriers (average delays low -> high")

plt.tight_layout()
plt.show()


# build a timeseries dataset with datatime field as index
flight_timeseries_day_origin = flights.groupby(['origin', 'uniqueCarrier', 'date'])['flight_status'].value_counts().unstack()
flight_timeseries_day_origin.reset_index(inplace=True)
flight_timeseries_day_origin.set_index('date', inplace=True)
flight_timeseries_day_origin = flight_timeseries_day_origin.loc[flight_timeseries_day_origin['origin'].isin(problem_airport_best_worse)]
flight_timeseries_day_origin = flight_timeseries_day_origin.loc[flight_timeseries_day_origin['uniqueCarrier'].isin(problem_carrier_best_worse)]

flight_timeseries_day_origin = flight_timeseries_day_origin.groupby(['origin', 'uniqueCarrier']).resample('M').sum(numeric_only=True)
flight_timeseries_day_origin.reset_index(inplace=True)

flight_timeseries_day_origin['prop'] = flight_timeseries_day_origin['delayed'] / (flight_timeseries_day_origin['delayed'] + flight_timeseries_day_origin['on_time'])
flight_timeseries_month.fillna(0, inplace=True)
flight_timeseries_day_origin.head()


# create an overall average base using all data
flight_timeseries_month['prop'] = flight_timeseries_month['delayed'] / (flight_timeseries_month['delayed'] + flight_timeseries_month['on_time'])
flight_timeseries_month.fillna(0, inplace=True)


plt.figure(figsize = (18,4))
sns.lineplot(data=flight_timeseries_day_origin, x='date', y='prop', hue='uniqueCarrier', errorbar=None, linewidth=1, hue_order=problem_carrier_best_worse, palette='viridis_r')
sns.lineplot(data=flight_timeseries_month['prop'], color='red', linewidth=2)
plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.show()


plt.figure(figsize = (18,4))
sns.lineplot(data=flight_timeseries_day_origin, x='date', y='prop', hue='origin', errorbar=None, linewidth=1, hue_order=problem_airport_best_worse, palette='viridis_r')
sns.lineplot(data=flight_timeseries_month['prop'], color='red', linewidth=2)
plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.show()


# clear the garbage to free memory
gc.collect()

13299


plt.figure(figsize = [18,6])

ax1 = plt.subplot(1,3,1)
sns.barplot(data=df_heatmap_topn_best_worse, x='uniqueCarrier', y='lateAircraftDelay', hue='origin', order=problem_carrier_best_worse, hue_order=problem_airport_best_worse, 
            palette='viridis_r')
plt.axhline(y=df_heatmap_topn_best_worse['lateAircraftDelay'].mean(), color='r', linewidth=1)
plt.ylabel('Average delays (in minutes)')
xticks = ax1.get_xticks()
# ax1.tick_params(axis='x', colors=['red', 'blue'])
plt.xticks(xticks, problem_carrier_description_best_worse, rotation=90)
plt.title('LATE AIRCRAFT delays')
plt.figlegend(bbox_to_anchor=(0.5, 1.05), loc = 'upper center', title='airports', fancybox=True, ncol=len(problem_airport_best_worse))
plt.legend().set_visible(False)
plt.ylim(0,15)

# indicate which carriers are performing above and below average by setting xtick label to green or red
for ax in plt.gca().get_xticklabels()[:4]:
    ax.set_color('green')
    
for ax in plt.gca().get_xticklabels()[4:]:
    ax.set_color('red')


plt.subplot(1,3,2, sharex=ax1, sharey=ax1)
sns.barplot(data=df_heatmap_topn_best_worse, x='uniqueCarrier', y='carrierDelay', hue='origin', order=problem_carrier_best_worse, hue_order=problem_airport_best_worse, palette='viridis_r')
plt.axhline(y=df_heatmap_topn_best_worse['carrierDelay'].mean(), color='r', linewidth=1)
plt.legend().set_visible(False)
plt.ylabel('')
plt.xticks(xticks, problem_carrier_description_best_worse, rotation=90)
plt.title('CARRIER delays')

# indicate which carriers are performing above and below average by setting xtick label to green or red
for ax in plt.gca().get_xticklabels()[:4]:
    ax.set_color('green')
    
for ax in plt.gca().get_xticklabels()[4:]:
    ax.set_color('red')


plt.subplot(1,3,3, sharex=ax1, sharey=ax1)
sns.barplot(data=df_heatmap_topn_best_worse, x='uniqueCarrier', y='NASDelay', hue='origin', order=problem_carrier_best_worse, hue_order=problem_airport_best_worse, palette='viridis_r')
plt.axhline(y=df_heatmap_topn_best_worse['NASDelay'].mean(), color='r', linewidth=1)
plt.ylabel('')
plt.xticks(xticks, problem_carrier_description_best_worse, rotation=90)
plt.title('NAS delays')
plt.legend().set_visible(False)

# indicate which carriers are performing above and below average by setting xtick label to green or red
for ax in plt.gca().get_xticklabels()[:4]:
    ax.set_color('green')
    
for ax in plt.gca().get_xticklabels()[4:]:
    ax.set_color('red')

plt.suptitle('Delays by top 10 worse performing airport and carrier combinations', y=1.1)

plt.tight_layout()
plt.show()


df_heatmap_lateAircraftDelay = df_heatmap_topn_worse.pivot_table(values='lateAircraftDelay',index='uniqueCarrier',columns='origin', aggfunc=np.mean, sort=False)
df_heatmap_lateAircraftDelay = df_heatmap_lateAircraftDelay.loc[:, df_heatmap_lateAircraftDelay.max().sort_values().index]
df_heatmap_lateAircraftDelay = df_heatmap_lateAircraftDelay.loc[df_heatmap_lateAircraftDelay.max(axis=1).sort_values().index, :]
df_heatmap_lateAircraftDelay.fillna(0, inplace=True)


df_heatmap_carrierDelay = df_heatmap_topn_worse.pivot_table(values='carrierDelay',index='uniqueCarrier',columns='origin', aggfunc=np.mean, sort=False)
df_heatmap_carrierDelay = df_heatmap_carrierDelay.loc[:, df_heatmap_carrierDelay.max().sort_values().index]
df_heatmap_carrierDelay = df_heatmap_carrierDelay.loc[df_heatmap_carrierDelay.max(axis=1).sort_values().index, :]
df_heatmap_carrierDelay.fillna(0, inplace=True)


df_heatmap_NASDelay = df_heatmap_topn_worse.pivot_table(values='NASDelay',index='uniqueCarrier',columns='origin', aggfunc=np.mean, sort=False)
df_heatmap_NASDelay = df_heatmap_NASDelay.loc[:, df_heatmap_NASDelay.max().sort_values().index]
df_heatmap_NASDelay = df_heatmap_NASDelay.loc[df_heatmap_NASDelay.max(axis=1).sort_values().index, :]
df_heatmap_NASDelay.fillna(0, inplace=True)


plt.figure(figsize = (16,6))

ax1 = plt.subplot(1,3,1)
vs.cat_heatmap(df_heatmap_lateAircraftDelay, reason='Late Aircraft Delay')

# add descriptions to xticks
xticks = ax1.get_xticks()
xlabels = []
locs, labels = plt.xticks()
for loc, label in zip(locs,labels):  
    city = '{}:{}'.format(label.get_text(), airports.loc[label.get_text()]['airport'][:20])
    xlabels.append(city)
plt.xticks(xticks, xlabels, rotation=90)

plt.subplot(1,3,2, sharey=ax1, sharex=ax1)
vs.cat_heatmap(df_heatmap_NASDelay, reason='NAS Delay')

plt.subplot(1,3,3, sharey=ax1, sharex=ax1)
vs.cat_heatmap(df_heatmap_carrierDelay, reason='Carrier Delay')

plt.suptitle('Heatmaps showing carrier and departure airport average delays by reason for delay')
plt.tight_layout()
plt.show()


g = sns.FacetGrid(data=flight_timeseries_day_origin, col='uniqueCarrier', row='origin', margin_titles=True, ylim=(0, 0.7), 
                  row_order=problem_airport_best_worse, col_order=problem_carrier_best_worse)
g.map(sns.lineplot, 'date', 'prop', linewidth=1.5, color=BASE_COLOR)


g.map(sns.lineplot, data=flight_timeseries_month['prop'], color='green', linewidth=0.5)
g.refline(y=flight_timeseries_month["prop"].mean(), color='r')

# increase fonts of axis and labels
g.set_xlabels('Period', size=18)
g.set_ylabels('% delayed flights', size=16)

g.set_titles(col_template="{col_name}", row_template="{row_name}", fontweight='bold', size=18)
plt.ylabel('Number of delayed flights', fontsize=18)
plt.xlabel('Date', fontsize=18)

plt.show()


# get flights for the top 10 worse performing carriers, and selected carriers that is doing well overall as comparision
flight_stats = flights.loc[(flights['uniqueCarrier'].isin(problem_carrier_best_worse)) & (flights['origin'].isin(problem_airport_best_worse))]


# calculate the distance, number of flights and avg delay per lane/carrier
flight_stats2 = flight_stats.groupby(['origin', 'lane', 'uniqueCarrier'], as_index=False).agg(depDelay=('depDelay', np.mean),
                                                                                              arrDelay=('arrDelay', np.mean), 
                                                                                              nrFlights=('lane', np.size),
                                                                                              distance=('distance', np.mean),
                                                                                              taxiOut=('taxiOut', np.mean),
                                                                                              taxiIn=('taxiIn', np.mean))
# analyse only frequent flights
flight_stats2 = flight_stats2.loc[flight_stats2['nrFlights'] >= 300]
flight_stats2.head()


# avg flights per day (fpd) per lane
flight_stats_fpd = flight_stats.groupby(['lane', 'uniqueCarrier', 'year', 'month', 'day'], as_index=False).size()
flight_stats_fpd['size'] = flight_stats_fpd['size'].round(0)
flight_stats_fpd = flight_stats_fpd.loc[flight_stats_fpd['size'] >= 1]
flight_stats_fpd = flight_stats_fpd.groupby(['lane', 'uniqueCarrier'], as_index=False).agg(dailyFlights=('size', np.mean))
flight_stats_fpd.head()


flight_stats_final = pd.merge(flight_stats2, flight_stats_fpd, on=['lane', 'uniqueCarrier'], how='inner')
flight_stats_final.sort_values(by='dailyFlights', inplace=True)
flight_stats_final['dailyFlights'] = round(flight_stats_final['dailyFlights'],0)
flight_stats_final.head()


plt.figure(figsize = (30,6))
sns.violinplot(data=flight_stats_final, x='dailyFlights', y='distance', hue='uniqueCarrier', color=BASE_COLOR, palette='viridis_r', hue_order=problem_carrier_best_worse)
plt.xlim(0,5)
plt.ylim(0,3500)
plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.title('Number of flights per carrier per day')
plt.ylabel('Distance (in miles)')
plt.xlabel('Average repeat flights per day for same lane')
plt.show()


mean_distance = int(flight_stats_final['distance'].mean())

sns.barplot(data=flight_stats_final, x='dailyFlights', y='distance', hue='uniqueCarrier', linewidth=1, errorbar=None, hue_order=problem_carrier_best_worse, palette='viridis_r')
plt.axhline(y=mean_distance, color='r', linewidth=1)
plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.xlim(0,7)

# min_xlim, max_xlim = plt.xlim()
# plt.text(max_xlim*0.85, mean_distance*1.01, 'Mean: {:.0f}'.format(mean_distance), weight='bold')

plt.title('Number of flights and distance by carrier')
plt.ylabel('Distance (in miles)')
plt.xlabel('Average repeat flights per day for same lane')
plt.show()


mean_distance = int(flight_stats_final['distance'].mean())
sns.barplot(data=flight_stats_final, x='uniqueCarrier', y='distance', order=problem_carrier_best_worse, palette='viridis_r')
plt.axhline(y=mean_distance, color='r', linewidth=1)

min_xlim, max_xlim = plt.xlim()
plt.text(max_xlim*0.95, mean_distance*1.01, 'Mean')

# indicate which carriers are performing above and below average by setting xtick label to green or red
for ax in plt.gca().get_xticklabels()[:4]:
    ax.set_color('green')
    
for ax in plt.gca().get_xticklabels()[4:]:
    ax.set_color('red')

plt.title('Average flight distance per carrier')
plt.ylabel('Distance (in miles)')
plt.xlabel('Carrier')
plt.show()


sns.barplot(data=flight_stats_final, x='uniqueCarrier', y='dailyFlights', order=problem_carrier_best_worse, palette='viridis_r')
plt.axhline(y=flight_stats_final['dailyFlights'].mean(), color='r', linewidth=1)
plt.title('Average number of repeat flights by carrier')
plt.ylabel('Average repeat flights per day for same lane')
plt.xlabel('Carrier')

# indicate which carriers are performing above and below average by setting xtick label to green or red
for ax in plt.gca().get_xticklabels()[:4]:
    ax.set_color('green')
    
for ax in plt.gca().get_xticklabels()[4:]:
    ax.set_color('red')
    
plt.show()


# Define figure
fig,ax = plt.subplots(figsize = (10,6))

sns.barplot(data=flight_stats_final, hue='uniqueCarrier', y='taxiOut', x='origin', order=problem_airport_best_worse, palette='viridis_r', errorbar=None)
plt.axhline(y=flight_stats_final['taxiOut'].mean(), color='r', linewidth=1)
plt.legend(bbox_to_anchor=(1, 1), loc='upper left') 
plt.title('Number of flights and distance by carrier')
plt.show()


# convert notebook to html
os.system('jupyter nbconvert --to html Part_II_exploration.ipynb')

	year	month	dayofMonth	dayOfWeek	depTime	CRSDepTime	arrTime	CRSArrTime	uniqueCarrier	flightNum	tailNum	actualElapsedTime	CRSElapsedTime	airTime	arrDelay	depDelay	origin	dest	distance	taxiIn	taxiOut	cancelled	cancellationCode	diverted
11231950	2004	9	7	2	0 days 14:31:00	0 days 14:35:00	0 days 19:45:00	0 days 19:52:00	UA	554	N662UA	194.0	197.0	169.0	0.0	0.0	DEN	IAD	1452	8.0	17.0	False	NaN	False
16205183	2005	5	16	1	0 days 18:05:00	0 days 17:55:00	0 days 19:56:00	0 days 20:00:00	OO	6958	N934SW	51.0	65.0	35.0	0.0	10.0	ORD	TVC	224	4.0	12.0	False	NaN	False
7299179	2004	2	28	6	0 days 13:25:00	0 days 13:20:00	0 days 14:48:00	0 days 14:47:00	OO	6407	N934SW	83.0	87.0	168.0	1.0	5.0	SFO	EUG	451	2.0	12.0	False	NaN	False
11630907	2004	9	8	3	0 days 09:50:00	0 days 09:55:00	0 days 12:38:00	0 days 12:45:00	MQ	3975	N501BG	108.0	110.0	87.0	0.0	0.0	ORD	ALB	723	4.0	17.0	False	NaN	False
23551316	2006	5	13	6	0 days 06:56:00	0 days 07:05:00	0 days 08:49:00	0 days 08:51:00	AA	1675	N5WAAA	173.0	166.0	151.0	0.0	0.0	MCO	DFW	984	10.0	12.0	False	NaN	False

	carrier_description
uniqueCarrier
AAP	Armstrong Air Service Inc.
Y8	Yangtze River Express Airlines Company
M3	ABSA-Aerolinhas Brasileiras
TEN	Tennessee Airways Inc.
NY	New York Air Inc.

	type	manufacturer	issue_date	model	status	aircraft_type	engine_type	year
tailnum
N14991	Corporation	EMBRAER	10/20/2000	EMB-145LR	Valid	Fixed Wing Multi-Engine	Turbo-Fan	2000
N934AT	Corporation	BOEING	05/17/2000	717-200	Valid	Fixed Wing Multi-Engine	Turbo-Fan	2000
N612AS	Corporation	BOEING	11/03/1999	737-790	Valid	Fixed Wing Multi-Engine	Turbo-Jet	1999
N69603	Corporation	BOEING	02/14/1994	737-524	Valid	Fixed Wing Multi-Engine	Turbo-Fan	1994
N952DL	Corporation	MCDONNELL DOUGLAS AIRCRAFT CO	11/15/2007	MD-88	Valid	Fixed Wing Multi-Engine	Turbo-Fan	1990
N778NC	Corporation	MCDONNELL DOUGLAS	07/07/1998	DC-9-51	Valid	Fixed Wing Multi-Engine	Turbo-Jet	1979
N91539E	unknown	unknown	unknown	unknown	unknown	unknown	unknown	unknown
N914DE	Corporation	MCDONNELL DOUGLAS AIRCRAFT CO	03/01/1993	MD-88	Valid	Fixed Wing Multi-Engine	Turbo-Fan	1993
N821AE	Corporation	EMBRAER	03/22/2002	EMB-135KL	Valid	Fixed Wing Multi-Engine	Turbo-Jet	2002
N560UA	Corporation	BOEING	05/26/2004	757-222	Valid	Fixed Wing Multi-Engine	Turbo-Jet	1992

	airport	city	state	country	lat	long
iata
DEN	Denver Intl	Denver	CO	USA	39.858408	-104.667002
OAR	Marina Municipal	Marina	CA	USA	36.681903	-121.762449
C29	Morey	Middleton	WI	USA	43.114244	-89.530732
LLJ	Challis	Challis	ID	USA	44.522978	-114.217564
SET	St Charles County Smart	St Charles	MO	USA	38.929694	-90.429961
MHL	Marshall Memorial Municipal	Marshall	MO	USA	39.095755	-93.202879
56D	Wyandot County	Upper Sandusky	OH	USA	40.883361	-83.314532
N13	Bloomsburg Muni	Bloomsburg	PA	USA	40.997781	-76.436056
ORB	Orr Regional	Orr	MN	USA	48.015922	-92.856051
BMC	Brigham City	Brigham City	UT	USA	41.552391	-112.062263

	year	month	day	depTime	date	dateh
37424095	2008	4	22	0 days 09:13:00	2008-04-22	2008-04-22 09:00:00
24267276	2006	7	4	0 days 17:06:00	2006-07-04	2006-07-04 17:00:00
27448115	2006	12	17	0 days 20:56:00	2006-12-17	2006-12-17 20:00:00
12797027	2004	11	16	NaT	2004-11-16	NaT
32658820	2007	8	3	0 days 17:31:00	2007-08-03	2007-08-03 17:00:00

Udacity Data Analysis Nanodegree¶

Project 5: Communicate data findings¶

Part II - Airline on-time performance - Exploration¶

by Juanita Smith¶

Have you ever been stuck in an airport because your flight was delayed or cancelled and wondered if you could have predicted it if you'd had more data? This is our chance to find out!¶

Dictionary:¶

Numerical exploration¶

Time Series Exploration¶

Let's start by looking of the distribution of flights vs delays per year¶

Can we spot peaks in months ? For example are summer months and xmas causing delays at airports ?¶

Plot proportion total flights vs delayed flights per month, but this time plot them seperately with different axis, to view the variation in months better.¶

Can we spot peaks in certain days of the month ?¶

Can we spot peaks in certain days of the week ?¶

Can we spot peaks in certain times of a day ?¶

Can we confirm the seasonal patterns for summer and xmas periods in time series plotting?¶

Use statsmodels auto and partial correlation to pickup seasonal patterns¶

Numerical & Time Series Summary:¶

Categorical analysis¶

Discuss the distribution(s) of your variable(s) of interest. Were there any unusual points? Did you need to perform any transformations?¶

Of the features you investigated, were there any unusual distributions? Did you perform any operations on the data to tidy, adjust, or change the form of the data? If so, why did you do this?¶

Numerical exploration¶

Categorical exploration¶

Let's zoom into the problem quadrant where carrier/airport combinations cause the biggest delays. Can we identify the reasons why these specific carrier/airports combinations are different?¶

Lets first look at the airports in the heatmap, and their reasons for delay¶

Lets first look at the carriers in the heatmap, and their reasons for delay¶

Time series exploration¶

Talk about some of the relationships you observed in this part of the investigation. How did the feature(s) of interest vary with other features in the dataset?¶

Did you observe any interesting relationships between the other features (not the main feature(s) of interest)?¶

Lets look at combination of carrier and airport in the problem quadrant¶

Create heatmap for each reason for delay to get a clearer picture of relationships¶

Observe the average flight distance per carrier¶

Observe the average number of repeat flights per carrier¶

Lastly, can we use taxiOut/taxiIn to see if smaller airlines have low priority at larger airports, e.g. departure from a gate vs parked and need to use bus to transport passengers?¶

Talk about some of the relationships you observed in this part of the investigation. Were there features that strengthened each other in terms of looking at your feature(s) of interest?¶

Were there any interesting or surprising interactions between features?¶

	year	month	day	dayOfWeek	depTime	arrTime	uniqueCarrier	tailNum	actualElapsedTime	airTime	depDelay	origin	dest	distance	taxiIn	taxiOut	cancelled	cancellationCode	diverted	hour	dateh	date	flight_status	lane
34227668	2007	11	27	2	0 days 07:07:00	0 days 08:07:00	XE	N11544	60.0	43.0	0.0	LGB	FAT	222	4.0	13.0	False	NaN	False	7.0	2007-11-27 07:00:00	2007-11-27	on_time	LGB-FAT
8868019	2004	5	3	1	0 days 14:48:00	0 days 18:17:00	US	N435US	149.0	135.0	3.0	MSY	PHL	1088	6.0	8.0	False	NaN	False	14.0	2004-05-03 14:00:00	2004-05-03	on_time	MSY-PHL
20835011	2006	1	17	2	0 days 07:00:00	0 days 08:02:00	WN	N629SW	62.0	47.0	0.0	BWI	BUF	281	2.0	13.0	False	NaN	False	7.0	2006-01-17 07:00:00	2006-01-17	on_time	BWI-BUF
36903216	2008	3	23	7	0 days 10:52:00	0 days 13:07:00	FL	N899AT	135.0	123.0	0.0	PHF	TPA	722	5.0	7.0	False	NaN	False	10.0	2008-03-23 10:00:00	2008-03-23	on_time	PHF-TPA
3853299	2003	8	22	5	0 days 07:35:00	0 days 07:37:00	WN	N409	62.0	53.0	0.0	JAX	BHM	365	2.0	7.0	False	NaN	False	7.0	2003-08-22 07:00:00	2003-08-22	on_time	JAX-BHM

	depDelay	arrDelay
count	36991148.0	36913964.0
mean	NaN	NaN
std	NaN	NaN
min	0.0	0.0
25%	0.0	0.0
50%	0.0	0.0
75%	7.0	11.0
85%	20.0	24.0
90%	33.0	38.0
95%	62.0	67.0
99%	146.0	154.0
99.9%	302.0	309.0
max	2600.0	2598.0

	sum	mean	perc
variable
lateAircraftDelay	138871488	18.551466	36.092073
NASDelay	120850739	16.144123	31.408561
carrierDelay	102318718	13.668480	26.592173
weatherDelay	21893841	2.924739	5.690110
securityDelay	835270	0.111581	0.217083

flight_status	delayed	diverted	on_time	total_flights	diff_delay	pct_delay
dateh
2003-01-01 00:00:00	6.0	NaN	25.0	31.0	NaN	NaN
2003-01-01 01:00:00	5.0	NaN	14.0	19.0	-1.0	-0.166667
2003-01-01 02:00:00	NaN	NaN	3.0	3.0	NaN	0.000000
2003-01-01 03:00:00	1.0	NaN	NaN	1.0	NaN	-0.800000
2003-01-01 04:00:00	NaN	NaN	1.0	1.0	NaN	0.000000

flight_status	delayed	diverted	on_time	total_flights	diff_delay	pct_delay
dateh
2008-04-30 19:00:00	200.0	1.0	1004.0	1205.0	27.0	0.156069
2008-04-30 20:00:00	167.0	NaN	659.0	826.0	-33.0	-0.165000
2008-04-30 21:00:00	119.0	NaN	607.0	726.0	-48.0	-0.287425
2008-04-30 22:00:00	52.0	NaN	212.0	264.0	-67.0	-0.563025
2008-04-30 23:00:00	17.0	NaN	69.0	86.0	-35.0	-0.673077

flight_status	origin	uniqueCarrier	date	delayed	on_time	prop
0	ATL	AA	2003-01-31	109.0	644.0	0.144754
1	ATL	AA	2003-02-28	138.0	535.0	0.205052
2	ATL	AA	2003-03-31	116.0	636.0	0.154255
3	ATL	AA	2003-04-30	120.0	614.0	0.163488
4	ATL	AA	2003-05-31	174.0	573.0	0.232932